﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Configuration;
using Vacation.Models;
using System.Data.SqlClient;
using System.Data;
using Vacation.DAL;

namespace Vacation.Controllers
{
    public class CourseController : Controller
    {

        private string connectionString;

        #region 连接数据库
        public CourseController()
        {
            ConnectionStringSettings ConnectionStringSettings = ConfigurationManager.ConnectionStrings["Vacation"];

            if ((ConnectionStringSettings == null) || (ConnectionStringSettings.ConnectionString.Trim() == String.Empty))
            {
                throw new Exception("连接字符串为空");
            }
            connectionString = ConnectionStringSettings.ConnectionString;
        }
        #endregion

        //课程列表页
        // GET: /Course/
         [Authorize]
        public ActionResult Index(string courseno,string name,string school,string type,int? page)
        {
           // int iRecordCount = 0;
            page =page??1;
            courseno = courseno ?? "%";
            name = name ?? "%";
            school = school ?? "%";
            type = type ?? "%";
            int total = 0;//总数
            int pagesize = 0;//页大小
            int pagecount = 0;

            List<Course> Courses=new List<Course>();
            Course course = null;

            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("Courses_Sel", sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            SqlDataReader sqlDataReader = null;
            try
            {
                sqlCommand.Parameters.Add("@page", SqlDbType.Int).Value = page;
                sqlCommand.Parameters.Add("@courseno", SqlDbType.NVarChar, 20).Value = courseno;
                sqlCommand.Parameters.Add("@name", SqlDbType.NVarChar, 60).Value = name;
                sqlCommand.Parameters.Add("@school", SqlDbType.NVarChar, 2).Value = school;
                sqlCommand.Parameters.Add("@type", SqlDbType.NVarChar, 1).Value = type;
                sqlConnection.Open();
                //默认会返回
                sqlDataReader = sqlCommand.ExecuteReader();

                if (sqlDataReader.Read())
                    ViewBag.Title = sqlDataReader["typename"];
                else
                    ViewBag.Title = "类型未知";
                sqlDataReader.NextResult();
                if (sqlDataReader.HasRows)
                {
                    sqlDataReader.Read();
                    total = Convert.ToInt32(sqlDataReader["amount"]);
                    pagesize = Convert.ToInt32(sqlDataReader["pagesize"]);
                }
                sqlDataReader.NextResult();

                while (sqlDataReader.Read())
                {
                    course = new Course();
                    course.CourseNo = sqlDataReader["CourseNo"].ToString();
                    course.Name = sqlDataReader["Name"].ToString();
                    course.School = sqlDataReader["School"].ToString();
                    course.Teacher = sqlDataReader["Teacher"].ToString();
                    course.Type = sqlDataReader["Type"].ToString();
                    course.Date = Convert.ToDateTime(sqlDataReader["Date"]);
                    Courses.Add(course);
                }
                pagecount = (int)Math.Ceiling(total*1.0 / pagesize );
                ViewBag.CourseNo = courseno;
                ViewBag.Name = name;
                ViewBag.SchoolCode = school;
                ViewBag.coursetype = type;
                
                //上一页
                if (page - 1 > 0)
                    ViewBag.LastPage = page - 1;
                else
                    ViewBag.LastPage=1;
                //下一页
                if (page + 1 > pagecount)
                    ViewBag.NextPage = pagecount;
                else
                    ViewBag.NextPage = page + 1;
                //最后页
                ViewBag.CurrPag = page;
                ViewBag.EndPage = pagecount;
                ViewBag.PageCount = pagecount;
                ViewBag.RecordCount = total;
                ViewBag.PageSize = pagesize;



            }
            catch (SqlException e)
            {
                  throw e;
            }
            finally
            {
                if (sqlDataReader != null)
                {
                    sqlDataReader.Close();
                }

                sqlConnection.Close();
            }
            //默认改为当前用户学院
            ViewBag.School = DataAccess.getSelectList(connectionString, "Schools_Sel", "%",true);
            return View(Courses);
        }

        //
        // GET: /Course/Details/5
         [Authorize]
        public ActionResult Details(string CourseNo)
        {
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("Course_Sel", sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            SqlDataReader sqlDataReader = null;
            try
            {
                sqlCommand.Parameters.Add("@courseno", SqlDbType.NVarChar, 7).Value = CourseNo;
                sqlConnection.Open();
                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

                if (sqlDataReader.HasRows)
                {
                    sqlDataReader.Read();
                    Course course = new Course();
                    course.CourseNo = sqlDataReader["CourseNo"].ToString();
                    course.Name = sqlDataReader["Name"].ToString();
                    course.School = sqlDataReader["School"].ToString();
                    course.Teacher = sqlDataReader["Teacher"].ToString();
                    course.Type = sqlDataReader["TypeName"].ToString();
                    course.Date = Convert.ToDateTime(sqlDataReader["Date"]);

                    return View(course);
                }
                else
                {
                    //返回错误信息。
                }

            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                if (sqlDataReader != null) { sqlDataReader.Close(); }
            }
            return Content("检索失败");
        }

        //进入创建课程页面
        // GET: /Course/Create
        [Authorize]
        public ActionResult Create(string Type)
        {
            Course course = new Course();
            string typename=DataAccess.getTypeName(connectionString, "CourseType_TypeName_Sel", Type);
            if (typename == "")
                return Content("课程类型代码错误！");
            else
            {
                course.Type = Type;
                ViewBag.Title = typename;
                return View(course);
            }
        } 

        //创建课程
        // POST: /Course/Create
        [HttpPost]
        [Authorize]
        public ActionResult Create(Course model)
        {
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("Course_Ins", sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            try
            {
                if (ModelState.IsValid)
                {
                    sqlCommand.Parameters.Add("@courseno", SqlDbType.NVarChar,7).Value=model.CourseNo;
                    sqlCommand.Parameters.Add("@name", SqlDbType.NVarChar,100).Value=model.Name;
                    sqlCommand.Parameters.Add("@type", SqlDbType.NVarChar,1).Value = model.Type;
                    sqlCommand.Parameters.Add("@date", SqlDbType.DateTime).Value = DateTime.Now;
                    sqlCommand.Parameters.Add("@teacherno", SqlDbType.NVarChar, 6).Value = User.Identity.Name;

                    sqlConnection.Open();
                    if (sqlCommand.ExecuteNonQuery() == 1)
                    {
                        return RedirectToAction("Index", new { type = model.Type });
                    }
                    else
                    {
                        ModelState.AddModelError("","课程已存在");
                    }
                }  
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                sqlConnection.Close();
            }
            ViewBag.School = DataAccess.getSelectList(connectionString, "Schools_Sel", model.School,false);
            ViewBag.Type = DataAccess.getSelectList(connectionString, "CourseTypes_Sel", model.Type,false);
            return View(model);
        }
        
        //编辑课程信息页
        // GET: /Course/Edit?CourseNo=010A01A
         [Authorize]
        public ActionResult Edit(string CourseNo)
        {
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("Course_Sel", sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            SqlDataReader sqlDataReader = null;
            try
            {
                sqlCommand.Parameters.Add("@courseno", SqlDbType.NVarChar, 7).Value = CourseNo;
                sqlConnection.Open();
                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

                if (sqlDataReader.HasRows)
                {
                    sqlDataReader.Read();
                    Course course = new Course();
                    course.CourseNo = sqlDataReader["CourseNo"].ToString();
                    course.Name = sqlDataReader["Name"].ToString();
                    course.School = sqlDataReader["School"].ToString();
                    course.Teacher = sqlDataReader["Teacher"].ToString();
                    course.Type = sqlDataReader["Type"].ToString();
                    course.Date = Convert.ToDateTime(sqlDataReader["Date"]);
                    ViewBag.Title = sqlDataReader["TypeName"].ToString();
                    return View(course);
                }
                else
                {
                    //返回错误信息。
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                if (sqlDataReader != null){sqlDataReader.Close();}
            }
            return Content("检索失败");
        }

        //提交编辑信息
        // POST:  /Course/Edit
        [HttpPost]
        [Authorize]
        public ActionResult Edit(Course model)
        {
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("Course_Upd", sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            try
            {
                if (ModelState.IsValid)
                {
                    sqlCommand.Parameters.Add("@courseno", SqlDbType.NVarChar, 7).Value = model.CourseNo;
                    sqlCommand.Parameters.Add("@name", SqlDbType.NVarChar, 100).Value = model.Name;
                    sqlCommand.Parameters.Add("@date", SqlDbType.DateTime).Value = DateTime.Now;
                    sqlCommand.Parameters.Add("@teacherno", SqlDbType.NVarChar, 6).Value = User.Identity.Name;
                    sqlConnection.Open();
                    if (sqlCommand.ExecuteNonQuery() == 1)
                    {
                        return RedirectToAction("Index", new { type = model.Type });
                    }
                    else
                    {
                        ModelState.AddModelError("", "更新失败");
                    }
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                sqlConnection.Close();
            }
            ViewBag.Type = DataAccess.getSelectList(connectionString, "CourseTypes_Sel", model.Type,false);
            return View(model);
        }

        //删除确认
        // GET: /Course/Delete/5
         [Authorize]
        public ActionResult Delete(string CourseNo)
        {
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("Course_Sel", sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            SqlDataReader sqlDataReader = null;
            try
            {
                sqlCommand.Parameters.Add("@courseno", SqlDbType.NVarChar, 7).Value = CourseNo;
                sqlConnection.Open();
                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

                if (sqlDataReader.HasRows)
                {
                    sqlDataReader.Read();
                    Course course = new Course();
                    course.CourseNo = sqlDataReader["CourseNo"].ToString();
                    course.Name = sqlDataReader["Name"].ToString();
                    course.School = sqlDataReader["School"].ToString();
                    course.Teacher = sqlDataReader["Teacher"].ToString();
                    course.Type = sqlDataReader["TypeName"].ToString();
                    course.Date = Convert.ToDateTime(sqlDataReader["Date"]);

                    return View(course);
                }
                else
                {
                    //返回错误信息。
                }

            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                if (sqlDataReader != null) { sqlDataReader.Close(); }
            }
            return Content("检索失败");
        }
        //删除操作
        // POST: /Course/Delete/5
        [HttpPost]
        [Authorize]
        public ActionResult Delete(string CourseNo,FormCollection x)
        {
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("Course_Del", sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            try
            {
                sqlCommand.Parameters.Add("@courseno", SqlDbType.NVarChar, 7).Value = CourseNo;
                sqlConnection.Open();
                if (sqlCommand.ExecuteNonQuery() == 1)
                {
                    return RedirectToAction("Index", new { Type="A" });
                }
                else
                {
                    return Content("删除条目失败");
                }
            }
            catch (SqlException e)
            {
                ModelState.AddModelError("", "删除条目失败");
                throw e;
            }
            finally
            {
                sqlConnection.Close();
            }
        }
        [Authorize]
        public ActionResult Score(string CourseNo)
        {
            ScoreModel scoreModel = new ScoreModel();
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("CourseStudent_Sel", sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            SqlDataReader sqlDataReader = null;
            try
            {
                sqlCommand.Parameters.Add("@courseno", SqlDbType.NVarChar, 7).Value = CourseNo;
                sqlConnection.Open();
                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);


                if (sqlDataReader.HasRows)
                {
                    sqlDataReader.Read();
                    scoreModel.CourseNo = CourseNo;
                    scoreModel.Name = sqlDataReader["name"].ToString();
                    scoreModel.School = sqlDataReader["school"].ToString();
                    scoreModel.Type = sqlDataReader["type"].ToString();
                    scoreModel.Total = Convert.ToInt32(sqlDataReader["total"]);
                    scoreModel.Normal = Convert.ToInt32(sqlDataReader["normal"]);
                    scoreModel.NoScore = Convert.ToBoolean(sqlDataReader["noscore"]);
                }
                else
                {
                    //返回错误信息。
                }
                string TeacherNo=User.Identity.Name;
                scoreModel.TeacherSchool=DataAccess.getTypeName(connectionString, "Teacher_School_Sel", TeacherNo);


            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                if (sqlDataReader != null) { sqlDataReader.Close(); }
            }
            return View(scoreModel);
        }
        [HttpPost]
        [Authorize]
        public ActionResult Score([ModelBinder(typeof(JsonBinder<ScoreModel>))]ScoreModel data)
        {
            if (User.Identity.Name == "")
            {
                return Content("您尚未登录");
            }

            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("Score_Ins", sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlConnection.Open();
            SqlTransaction tran = sqlConnection.BeginTransaction();
            SqlParameter studentno = sqlCommand.Parameters.Add("@studentno", SqlDbType.NVarChar, 9);
            SqlParameter score = sqlCommand.Parameters.Add("@score", SqlDbType.Decimal);
            sqlCommand.Parameters.Add("@courseno", SqlDbType.NVarChar, 7).Value = data.CourseNo;
            sqlCommand.Parameters.Add("@teacherno", SqlDbType.NVarChar, 6).Value = User.Identity.Name;
            //检查教师与课程是否同一学院。

            //提交数据
            int iTotal = data.studentScore.Count;
            IList<string> errorstudent=new List<string>();

            try
            {
                sqlCommand.Transaction = tran;
                for (int i = 0; i < iTotal ; i++)
                {
                    studentno.Value = data.studentScore[i].StudentNo;
                    score.Value = data.studentScore[i].Score;
                    if (sqlCommand.ExecuteNonQuery() != 1)
                    {
                        string student= data.studentScore[i].StudentNo;
                        errorstudent.Add(student);
                    }
                }
                tran.Commit();
            }
            catch (SqlException e)
            {
                try
                {
                    tran.Rollback();
                }
                catch { }
                throw (e);
            }
            finally
            {
                sqlConnection.Close();
            }
            return Json(errorstudent, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        [Authorize]
        public ActionResult searchStudent(string StudentNo, string ClassNo,string Page,string CourseNo)
        {
            StudentsModel students = new StudentsModel();
            students.studentscore = new List<StudentScore>();
            StudentScore studentscore;

            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("Students_Sel", sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            SqlDataReader sqlDataReader = null;
            try
            {
                sqlCommand.Parameters.Add("@studentno", SqlDbType.NVarChar, 15).Value = StudentNo;
                sqlCommand.Parameters.Add("@classno", SqlDbType.NVarChar, 13).Value = ClassNo;
                sqlCommand.Parameters.Add("@page",SqlDbType.Int).Value = Page;
                sqlCommand.Parameters.Add("@courseno", SqlDbType.NVarChar,7).Value =CourseNo;
                sqlConnection.Open();
                sqlDataReader = sqlCommand.ExecuteReader();

                if (sqlDataReader.HasRows)
                {
                    sqlDataReader.Read();
                    students.pagesize = Convert.ToInt32(sqlDataReader["pagesize"]);
                    students.total = Convert.ToInt32(sqlDataReader["amount"]);
                    sqlDataReader.NextResult();
                    while (sqlDataReader.Read())
                    {
                        studentscore = new StudentScore();
                        studentscore.StudentNo = sqlDataReader["studentno"].ToString();
                        studentscore.Name = sqlDataReader["name"].ToString();
                        studentscore.Class = sqlDataReader["class"].ToString();
                        studentscore.Score = 0;
                        students.studentscore.Add(studentscore);
                    }
                   

                }
                else
                {
                    //返回错误信息。
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                if (sqlDataReader != null) { sqlDataReader.Close(); }
                sqlConnection.Close();
            }
            return Json(students, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        [Authorize]
        public ActionResult searchScore(string StudentNo, string ClassNo, string Page, string CourseNo,int Teacher)
        {
            StudentsModel students = new StudentsModel();
            students.studentscore = new List<StudentScore>();
            StudentScore studentscore;

            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("Scores_Sel", sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            SqlDataReader sqlDataReader = null;
            try
            {
                sqlCommand.Parameters.Add("@studentno", SqlDbType.NVarChar, 15).Value = StudentNo;
                sqlCommand.Parameters.Add("@classno", SqlDbType.NVarChar, 13).Value = ClassNo;
                sqlCommand.Parameters.Add("@page", SqlDbType.Int).Value = Page;
                sqlCommand.Parameters.Add("@courseno", SqlDbType.NVarChar, 7).Value = CourseNo;
                if(Teacher==0)
                    sqlCommand.Parameters.Add("@teacherno", SqlDbType.NVarChar, 6).Value = "%";
                else
                    sqlCommand.Parameters.Add("@teacherno", SqlDbType.NVarChar, 6).Value = User.Identity.Name;
                sqlConnection.Open();
                sqlDataReader = sqlCommand.ExecuteReader();

                if (sqlDataReader.HasRows)
                {
                    sqlDataReader.Read();
                    students.pagesize = Convert.ToInt32(sqlDataReader["pagesize"]);
                    students.total = Convert.ToInt32(sqlDataReader["amount"]);
                    sqlDataReader.NextResult();
                    while (sqlDataReader.Read())
                    {
                        studentscore = new StudentScore();
                        studentscore.StudentNo = sqlDataReader["studentno"].ToString();
                        studentscore.Name = sqlDataReader["name"].ToString();
                        studentscore.Class = sqlDataReader["class"].ToString();
                        studentscore.Teacher = sqlDataReader["teacher"].ToString();
                        studentscore.Score =Convert.ToDecimal(sqlDataReader["score"]);
                        studentscore.Date =sqlDataReader["date"].ToString();
                        students.studentscore.Add(studentscore);
                    }
                }
                else
                {
                    //返回错误信息。
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                if (sqlDataReader != null) { sqlDataReader.Close(); }
                sqlConnection.Close();
            }
            return Json(students, JsonRequestBehavior.AllowGet);
        }
    }
}
